<?php

namespace core;

use mysqli;

require_once("SqlHelper.php");

AdminService::$db = new SqlHelper();
class AdminService
{
    public static $db;

    #region contructor
    function __construct()
    {
    }

    /**
     * 初始化静态类
     *
     * @return AdminService
     */
    public static function initialize(): AdminService
    {
        if (!self::$db instanceof self) {
            return new self();
        }

        return self::$db;
    }

    #endregion

    #region about paper

    /**
     * @description: 添加试卷
     * @param string $title
     * @param int $accountId
     * @param int $minutes
     * @param float $fraction
     * @param string $paras
     * @return void
     */
    // public static function AddPaper($title, $accountId, $minutes, $fraction, $paras)
    // {
    //     // FIXME 这个方法没用到
    //     $sql = "insert into test_paper(id, title,accountId, minutes, fraction ) values (NULL, '%s' ,$accountId, %d, %f) ";
    //     $sql = printf($sql, $title, $minutes, $fraction);
    //     $sql = mysqli_escape_string(static::$db->get_mysqli(), $sql);
    //     $paperId = static::$db->execute_dml($sql);


    //     $arys = explode(",", $paras);
    //     foreach ($arys as $item) {
    //         $kv = explode(" ", $item);
    //         $sql = "insert into ques_pape_builder (id,quesId,paperId,fraction) values (NULL,$kv[0],$paperId,$kv[1])";
    //         $num = static::$db->execute_dml($sql);
    //     }
    //     // ? 这里返回什么
    // }


    /**
     * @description: 添加试卷
     * @param {*} $title
     * @param {*} $aid
     * @param {*} $minutes
     * @param {*} $fraction
     * @return {*}
     */
    static function AddPaper($title, $aid, $minutes, $fraction)
    {
        $sql  = "INSERT INTO test_paper (title, accountId, minutes, fraction) VALUES ('$title', '$aid', '$minutes', 100)";
        $result = static::$db->execute_dml($sql);
        return $result;
    }

    /**
     * @description: 删除试卷
     * @param int $paperId
     * @return {*}
     */
    public static function DelPaper($paperId)
    {
        $sql = "delete from test_paper where id=$paperId";
        $num = static::$db->execute_dml($sql);

        $sql = "delete from ques_pape_builder where paperId=$paperId";
        $num = static::$db->execute_dml($sql);
    }

    public static function UpdatePaper($pid, $title, $minutes, $fraction)
    {
        $sql = "update test_paper set title='$title', minutes='$minutes', fraction='100' where id=$pid";
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    /**
     * 获取试卷
     */
    public static function GetPapers($page = 1, $limit = 15, $para = null)
    {
        $sql = "select a.id,a.title as t,case when b.name is null then '空' else b.name end as n,a.minutes as m ,a.createDate as cd from test_paper a left join people_account b on a.accountId=b.id where 1 ";

        // 搜索条件组装
        if ($para != null) {
            foreach ($para as $key => $value) {
                if (is_null($value) || trim($value) === '') continue;
                if ($key === 'title' || $key === 'name')
                    $sql .= ' and ' . $key . " like '%$value%'";
                else
                    $sql .= ' and ' . $key . "='$value'";
            }
        }
        $sql .= ' order by a.createDate desc ';
        $sql .= ' limit ' . $limit  . ' offset ' . ($page - 1) * $limit;
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }
    #endregion

    #region about ques
    /**
     * @description: 添加题目
     * @param object $json
     * @param {*} $accountId
     * @return {*}
     */
    public static function AddQuestion($args, $accountId)
    {
        $sql = "insert into ques (id,accountId,question,type,answer,A,B,C,D) values (NULL,$accountId,'%s','%s','%s','%s','%s','%s','%s')";
        $sql = vsprintf($sql, $args);
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    public static function UpdateQuestion($args, $id, $accountId)
    {
        $sql = "update ques set accountId=$accountId, question='%s', type='%s', answer='%s',A='%s',B='%s',C='%s',D='%s' where id=$id";
        $sql = vsprintf($sql, $args);
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    public static function GetQuestionByPaper($pid)
    {
        $sql = "select * from ques_pape_builder a left join ques b on a.quesId=b.id where a.paperId=$pid";
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function AssignPaper($pid, $no)
    {
        $sql = "update people_account set pid=$pid where no='$no'";
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    /**
     * @description: 选择题目到试卷
     * @param {*} $pid 试卷id
     * @param {*} $qid 题目id
     * @return {*}
     */
    static function AddQuesToPaper($pid, $qid)
    {
        $sql  = "INSERT INTO ques_pape_builder (paperId,quesId) VALUES(" . $pid . "," . $qid . ")";
        $result = static::$db->execute_dml($sql);
        return $result;
    }

    public static function DelQuesFromPaper($pid, $qid)
    {
        $sql = "delete from ques_pape_builder where paperId=$pid and quesId=$qid";
        $result = static::$db->execute_dml($sql);
        return $result;
    }

    public static function GetQuestions($page = 1, $limit = 15, $paras = null)
    {
        $sql = "select distinct a.id,case when b.id is null then '未知' else b.name end as n,a.question as q,a.answer as an,case a.type when 'radio' then '单选题' when 'checkbox' then '多选题' when 'judge' then '是非题' else '未知题型' end as t ,a.A ,a.B,a.C,a.D,a.createDate as cd from ques a left join people_account b on a.accountId=b.id where 1 ";

        $condition = '';
        if ($paras != null) {
            foreach ($paras as $key => $value) {
                if (is_null($value) || trim($value) === '') continue;
                if ($key === 'name') {
                    $condition .= " and b.$key like '%$value%' ";
                } else if ($key === 'question') {
                    $condition .= " and a.$key like '%$value%' ";
                } else {
                    $condition .= " and $key='$value' ";
                }
            }
        }
        $sql .= $condition;
        $sql .= " order by a.createDate desc ";
        $sql .= ' limit ' . $limit  . ' offset ' . ($page - 1) * $limit;
        $result = static::$db->execute_dql_assoc($sql);
        $num = static::$db->execute_dql_counts('ques', '*', $condition);

        $arr[] = $result;
        $arr[] = $num;
        return $arr;
    }

    public static function GetQuestion($id)
    {
        $sql = "select * from ques a where a.id=$id";
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function GetQuess()
    {
        $sql = "select group_concat(a.id) ids from ques a";
        $result = static::$db->execute_dql_num($sql);
        if (count($result) > 0) {
            $ary = $result[0][0];
            $ary = explode(",", $ary);
            return $ary;
        }
        return $result;
    }

    public static function DeleteQuestion($id)
    {
        $num = AdminService::DeleteByTalbe("ques", "id=$id");
        return $num;
    }

    public static function GetQuesArray($pid)
    {
        $sql = "select group_concat(a.quesId) qs from ques_pape_builder a where a.paperId=" . $pid;
        $result = static::$db->execute_dql_assoc($sql);
        if ($result && count($result) > 0) {
            $result = explode(",", $result[0]['qs']);
            return $result;
        }
        return null;
    }

    #endregion

    #region about people_account


    /**
     * @description: 添加人员
     * @param array $arys
     * @param int $type
     * @return int
     */
    public static function AddPeople($arys, $type = 1)
    {
        $sql = "insert into people_account (id,no,name,password,gender,level,phone,email,remarks) values (NULL,'%s','%s','%s','%s',$type,'%s','%s','%s')";
        $sql = vsprintf($sql, $arys);
        // $sql = \mysql_escape_string($sql);
        // $sql = mysqli_escape_string(static::$db->get_mysqli(), $sql);
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    public static function AddStudent($args)
    {
        $data_count = count($args, 0);
        $suc_count = 0;
        foreach ($args as $key => $value) {
            $sql = "insert into people_account (no,name,password,gender,level,email) values ()";
            $result = static::$db->execute_dml($sql);
            if ($result > 0) $suc_count++;
        }

        $rval[] = $suc_count;
        $rval[] = $data_count;
        return $rval;
    }

    public static function UpdatePeople($arys, $type = 1)
    {
        $sql = "update people_account set";
        foreach ($arys as $key => $value) {
            if ($key == "id") continue;

            if ($value != null && trim($value) != '') {
                $sql .= " $key='$value',";
            }
        }
        $sql = substr($sql, 0, strlen($sql) - 1);
        $sql .= " where level=$type and id=" . $arys['id'] . "";
        $num = static::$db->execute_dml($sql);
        return $num;
    }

    /**
     * @description: 获取用户
     * @param int $page
     * @param int $limit
     * @param int $level
     * @param object $para
     * @return array
     */
    public static function GetPeoples($page = 1, $limit = 15, $level = 1, $para = null)
    {
        $sql = 'SELECT a.id,a.no,a.pid,a.name as n,a.gender AS sex,a.phone as p,a.remarks as r,a.email as e, ';
        if ($level === 1) {
            $sql .= "case when b.createDate is null then '未考试' else b.createDate end as cd,case when b.score is null then 0 else b.score end as s FROM `people_account` a left join ques_record b on a.no=b.studentNo WHERE 1 ";
        } else {
            $sql .= 'a.createDate as cd FROM `people_account` a WHERE 1 ';
        }
        $sql .= ' and level=' . $level;
        // 搜索条件组装
        if ($para != null) {
            foreach ($para as $key => $value) {
                if (is_null($value) || trim($value) === '') continue;
                if ($key === 'no' || $key === 'name')
                    $sql .= ' and ' . $key . " like '%$value%'";
                else
                    $sql .= ' and ' . $key . "='$value'";
            }
        }
        $sql .= ' limit ' . $limit  . ' offset ' . ($page - 1) * $limit;
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function GetSinglePeople($id)
    {
        $sql = "select a.id,a.no,a.name as n,a.gender AS sex,a.phone as p,a.email as e ,a.remarks as r,a.createDate as cd FROM `people_account` a WHERE a.id=$id";
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function GetSinglePaper($id)
    {
        $sql = "SELECT a.id,a.title,a.accountId as aid,a.minutes as min,a.fraction as frac,a.createDate as cd FROM `test_paper` a where a.id=$id";
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function GetSingleQuestion($id)
    {
        $sql = "select a.id,a.question as ques,a.answer as ans,a.type,a.A,a.b,a.c,a.d,a.createDate as cd from ques a where a.id=$id";
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    public static function GetTestPeoples($page = 1, $limit = 15, $level = 1, $para = null)
    {
        $sql = 'SELECT * FROM `people_account` WHERE 1 ';
        $sql .= ' and level=' . $level;
        if ($para != null) {
            foreach ($para as $key => $value) {
                if (is_null($value) || trim($value) === '') continue;
                if ($key === 'no')
                    $sql .= ' and ' . $key . " like '%$value%'";
                else
                    $sql .= ' and ' . $key . "='$value'";
            }
        }
        $sql .= ' limit ' . $limit  . ' offset ' . ($page - 1) * $limit;
        $result = static::$db->execute_dql_num($sql);
        return $result;
    }


    public static function DelPeople($id, $type = 1)
    {
        $sql = 'DELETE FROM `people_account` WHERE id=' . $id . ' and level=' . $type;
        $result = static::$db->execute_dml($sql);
        return $result;
    }

    #endregion

    #region 未使用
    public static function ExportExcel()
    {
        // 1
        // require_once('../utils/ExcelUtil.php');
        // exportExcel();


        // 2 
        // header("Content-type:application/vnd.ms-excel");
        // header("Content-Disposition:attachment;filename=test_data.xls");
        // //输出内容如下： 
        // echo   "姓名" . "\t";
        // echo   "生日" . "\t[这有一个空格]";
        // echo   "学历" . "\t";
        // echo   "\n";
        // echo   "张三" . "\t";
        // echo   "1992-10-12" . "\t";
        // echo   "本科" . "\t";
        // exit();


        // 3
        header("Content-type: text/html; charset=gb2312");
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=test_data.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        $data = static::GetTestPeoples();
        $html = "";
        foreach ($data as $value) {
            $html = $html . "<tr><td align='center'>" . $value[0] . "</td>"
                . "<td align='center'>" . $value[1] . "</td>"
                . "<td align='center' >" . $value[2] . " " . "</td>"
                . "<td align='center' style='vnd.ms-excel.numberformat:yyyy-mm-dd hh:mm'>" . $value[3] . "</td>"
                . "<td align='center' style='vnd.ms-excel.numberformat:yyyy-mm-dd hh:mm'>" . $value[4] . "</td>"
                . "<td align='center'>" . $value[5] . "</td>";
        }
        $html = "<table border='1'>" . $html . "</table>";
        echo $html;
        exit();
    }

    /**
     * @description: 记录分数
     * @param string $answers
     * @return {*}
     */
    public static function RecordScore($answers)
    {
        // TODO resolve
    }

    /**
     * @description: 
     * @param {*} $table
     * @param {*} $fields
     * @param {*} $where
     * @param {*} $groupby
     * @param {*} $orderby
     * @return array
     */
    public static function SelectTable($table, $fields = null, $where = null, $groupby = null, $orderby = null)
    {
        $sql = "select " . ($fields === null ? '*' : $fields) . " from " . $table;
        if ($where != null) $sql .= " WHERE " . $where;
        if ($groupby != null) $sql .= " group by " . $groupby;
        if ($orderby != null) $sql .= " order by " . $where;
        $result = static::$db->execute_dql_assoc($sql);
        return $result;
    }

    #endregion 


    #region ordinary methods
    public static function DeleteByTalbe($tableName, $whereCondition)
    {
        $sql = "delete from $tableName where 1 and $whereCondition";
        $num = static::$db->execute_dml($sql);
        return $num;
    }
    #endregion
}
